/**
 * 版权所有 2009-2015 中国中医科学院中医药信息研究所 何前锋 frontpeak@yahoo.com.cn
 **/
package cn.ac.cintcm.sh.dao;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;

import cn.ac.cintcm.prespring.util.IndexFile;
import cn.ac.cintcm.sh.domain.Herb;
import cn.ac.cintcm.sh.domain.HerbEntry;
import cn.ac.cintcm.sh.domain.Prescription;
import cn.ac.cintcm.sh.domain.Unit;
import cn.ac.cintcm.sh.service.AliasService;

public class PrescriptionDao {
    
    private HsqldbDao dao = (new HsqldbDao()).getDao();
    private HerbDao herbDao = new HerbDao();

    public List<Prescription> getIndex() {
        List<Prescription> nodes = new ArrayList<Prescription>();
        String sql="select id, name from prescription";
        dao.openConnection();
        ResultSet rs=this.dao.executeQuery(sql);
        try{
            while(rs.next()){
                int id=rs.getInt("id");
                String name=rs.getString("name");
                Prescription node=new Prescription();
                node.setId(id);
                node.setName(name);
                nodes.add(node);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        dao.closeConnection();
        return nodes;
    }

    public List<Prescription> getPrescriptions(String name, boolean precise) {
        List<Prescription> nodes = new ArrayList<Prescription>();

        String sql="select id, name from prescription where name ";
        if (precise) {
            sql = sql + "='" + name;
        } else {
            sql = sql + " like '%" + name + "%";
        }
        sql = sql + "'";
        dao.openConnection();
        ResultSet rs=this.dao.executeQuery(sql);
        try{
            while(rs.next()){
                int id=rs.getInt("id");
                String pname=rs.getString("name");
                Prescription node=new Prescription();
                node.setId(id);
                node.setName(pname);
                nodes.add(node);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        dao.closeConnection();
        return nodes;
    }

    /**
     * 取得包含所有中药的方剂
     * @param herbList 中药的名称列表
     * @return 包含有所有中药的方剂
     */
    public List<Prescription> getPrescriptions(String[] herbList, boolean extendAlias) {
        List<Prescription> nodes = null;
        List<String> formalList = null;
        String tableName = null;
        if (extendAlias) {
            formalList = AliasService.getFormal(herbList);
            tableName = "herb_pres";
        } else {
            formalList = Arrays.asList(herbList);
            tableName = "herb_in_prescription";
        }
        if (formalList != null && formalList.size() > 0) {
            List<HerbEntry> hes = herbDao.getHerbs(formalList);
            List<Integer> ids = new ArrayList<Integer>();
            for (HerbEntry he : hes) {
                ids.add(he.getId());
            }
            nodes  = getPrescriptions(ids, tableName);
        }
        return nodes;
    }
    
    /**
     * 取得包含所有中药的方剂
     * @param herbList 中药的id列表
     * @return 包含有所有中药的方剂
     */
    public List<Prescription> getPrescriptions(String[] herbList) {
        List<Prescription> nodes = null;
        String tableName = "herb_in_prescription";
        if (herbList != null && herbList.length > 0) {
            
            List<Integer> ids = new ArrayList<Integer>();
            for (String he : herbList) {
                ids.add(Integer.parseInt(he));
            }
            nodes  = getPrescriptions(ids, tableName);
        }
        return nodes;
    }

    private List<Prescription> getPrescriptions(List<Integer> ids, String tableName) {
        List<Prescription> nodes = null;
        if (ids != null && ids.size() > 0) {
            nodes = new ArrayList<Prescription>();
            String sqls = "select prescription_id from "+tableName+" where id=";
            String intersect = " intersect ";
            StringBuffer sql = new StringBuffer();
            sql.append("select * from prescription where id in (");
            for (Integer id : ids) {
                sql.append(sqls);
                sql.append(id);
                sql.append(intersect);
            }
            sql.delete(sql.length() - intersect.length() + 1, sql.length() - 1);
            sql.append(")");
            dao.openConnection();
            ResultSet rs=this.dao.executeQuery(sql.toString());
            if (rs != null) {
                try{
                    while(rs.next()){
                        int id=rs.getInt("id");
                        String pname=rs.getString("name");
                        Prescription node=new Prescription();
                        node.setId(id);
                        node.setName(pname);
                        nodes.add(node);
                    }
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
            dao.closeConnection();
        }
        return nodes;
    }

    public List<Prescription> getPrescriptions(Integer[] herbs) {
        List<Prescription> nodes = null;
        String tableName = "herb_in_prescription";
        if (herbs!= null) {
            nodes  = getPrescriptions(Arrays.asList(herbs), tableName);
        }
        return nodes;
    }

    public List<Prescription> getPrescriptions(float minv, float maxv) {
        List<Prescription> nodes = new ArrayList<Prescription>();
        String sql="select id, name from prescription where HOTVALUE between " + minv + " and " + maxv;
        dao.openConnection();
        ResultSet rs=this.dao.executeQuery(sql);
        try{
            while(rs.next()){
                int id=rs.getInt("id");
                String name=rs.getString("name");
                Prescription node=new Prescription();
                node.setId(id);
                node.setName(name);
                nodes.add(node);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        dao.closeConnection();
        return nodes;
    }

    public Prescription getPrescription(int pid) {
        String sql="select herb_in_prescription.id, herb_in_prescription.dosage, " +
                "herb_in_prescription.unit,prescription.name, prescription.text," +
                "prescription.hotvalue,herbentry.name from herb_in_prescription, " +
                "prescription, herbentry where prescription.id= herb_in_prescription.prescription_id " +
                "and herbentry.id=herb_in_prescription.id and prescription.id=" + pid;
        dao.openConnection();
        ResultSet rs=this.dao.executeQuery(sql);
        Prescription node=new Prescription();
        try{
            while (rs.next()){
                int id=rs.getInt(1);
                float dosage = rs.getFloat(2);
                String unit = rs.getString(3);
                String name = rs.getString(4);
                String composition = rs.getString(5);
                float hcvalue = rs.getFloat(6);
                String herbName = rs.getString(7);
                Herb h = new Herb();
                h.setId(id);
                h.setName(herbName);
                Unit hunit = new Unit();
                hunit.setQuantity(dosage);
                hunit.setUnit(unit);
                h.setDosage(hunit);
                node.addHerb(h);
                node.setId(pid);
                node.setName(name);
                node.setText(composition);
                node.setQiValue(hcvalue);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        dao.closeConnection();
        return node;
    }
    
    public Collection<Prescription> getPrescriptionsAll(String[] pids) {
        StringBuilder sql= new StringBuilder();
        sql.append("select herb_in_prescription.id, herb_in_prescription.dosage, " +
                "herb_in_prescription.unit,prescription.name, prescription.text," +
                "prescription.hotvalue,herbentry.name,prescription.id from herb_in_prescription, " +
                "prescription, herbentry where prescription.id= herb_in_prescription.prescription_id " +
                "and herbentry.id=herb_in_prescription.id and prescription.id in(");
        for (String name : pids) {
            sql.append("'");
            sql.append(name);
            sql.append("',");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")");
        dao.openConnection();
        ResultSet rs=this.dao.executeQuery(sql.toString());
        HashMap<Integer, Prescription> ps = new HashMap<Integer, Prescription>();
        try{
            while (rs.next()){
                int pid = rs.getInt(8);
                Prescription node= ps.get(pid);
                String name = rs.getString(4);
                String composition = rs.getString(5);
                float hcvalue = rs.getFloat(6);
                if (node == null) {
                    node = new Prescription();
                    node.setId(pid);
                    node.setName(name);
                    node.setText(composition);
                    node.setQiValue(hcvalue);
                    ps.put(pid, node);
                }
                int id=rs.getInt(1);
                float dosage = rs.getFloat(2);
               
                String herbName = rs.getString(7);
                
                Herb h = new Herb();
                h.setId(id);
                h.setName(herbName);
                
                Unit hunit = new Unit();
                hunit.setQuantity(dosage);
                String unit = rs.getString(3);
                hunit.setUnit(unit);
                h.setDosage(hunit);
                
                node.addHerb(h);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        dao.closeConnection();
        return ps.values();
    }
    
    public IndexFile getIndexFile() {
        IndexFile indexFile = null;
        HashMap<Integer, List<Integer>> indexs = new HashMap<Integer, List<Integer>>();
        String sql = "select id,prescription_id from herb_in_prescription";
        dao.openConnection();
        ResultSet rs=this.dao.executeQuery(sql);
        try {
            while (rs.next()) {
                Integer hid = rs.getInt("id");
                Integer pid = rs.getInt("prescription_id");
                List<Integer> list = indexs.get(pid);
                if (list==null) {
                    list = new ArrayList<Integer>();
                    indexs.put(pid, list);
                }
                list.add(hid);
            }
            indexFile = new IndexFile(indexs);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            dao.closeConnection();
        }
       
        return indexFile;
    }

    public HashMap<Integer, String> getNameIndex() {
        HashMap<Integer, String> nameIndex = new HashMap<Integer, String>();
        String sql = "select id,name from prescription";
        dao.openConnection();
        ResultSet rs=this.dao.executeQuery(sql);
        try {
            while (rs.next()) {
                Integer id = rs.getInt(1);
                String name = rs.getString(2);
                nameIndex.put(id, name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dao.closeConnection();
        }
        return nameIndex;
    }

    public List<Prescription> getPrescriptions(List<Integer> simIds) {
        List<Prescription> nodes = new ArrayList<Prescription>();
        if (simIds != null && simIds.size()>0) {
            StringBuilder sql=new StringBuilder();
            sql.append("select * from prescription where id in(");
            for (Integer name : simIds) {
                sql.append("'");
                sql.append(name);
                sql.append("',");
            }
            sql.deleteCharAt(sql.length() - 1);
            sql.append(")");
            dao.openConnection();
            ResultSet rs=this.dao.executeQuery(sql.toString());
            try{
                while(rs.next()){
                    int id=rs.getInt("id");
                    String name=rs.getString("name");
                    String text = rs.getString("text");
                    float hcValue = rs.getFloat("hotvalue");
                    Prescription node=new Prescription();
                    node.setId(id);
                    node.setName(name);
                    node.setText(text);
                    node.setQiValue(hcValue);
                    nodes.add(node);
                }
            }catch(Exception e){
                e.printStackTrace();
            }
            dao.closeConnection();
        }
        return nodes;

    }

}